package com.hc.sort.server.dao;

import java.sql.*;
import java.util.*;
import java.util.Date;

import com.hc.sort.server.pojo.*;
import com.hc.sort.utils.JdbcUtil;

public class TicketDaoImpl implements TicketDao {
	Connection conn=null;
	PreparedStatement pstmst=null;
	ResultSet rs=null;
	int row;

	@Override
	public int add(Ticket ticket) {
		String sql="insert into t_ticket values(null,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		
		try {
			conn=JdbcUtil.getConnection();
			pstmst=conn.prepareStatement(sql);
			
			pstmst.setObject(1, ticket.getNo());
			pstmst.setObject(2, ticket.getBusinessCode());
			pstmst.setObject(3, ticket.getBusinessName());
			pstmst.setObject(4, ticket.getTakeTime());
			pstmst.setObject(5, ticket.getTakeIp());
			pstmst.setObject(6, ticket.getWaitCount());
			pstmst.setObject(7, ticket.getCallTime());
			pstmst.setObject(8, ticket.getCallCount());
			pstmst.setObject(9, ticket.getCallIp());
			pstmst.setObject(10, ticket.getCallWindow());
			pstmst.setObject(11, ticket.getCallerWorkno());
			pstmst.setObject(12, ticket.getIsSuccess());
			pstmst.setObject(13, ticket.getDesc());
			
			row=pstmst.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JdbcUtil.close(null,pstmst,conn);
		}
		return row;
	}

	@Override
	public int delete(int id) {
		return 0;
	}

	@Override
	public int update(Ticket ticket) {
		String sql="update t_ticket set TICKET_NO=?,"
				+ "TICKET_BUSINESS_CODE=?,"
				+ "TICKET_BUSINESS_NAME=?,"
				+ "TICKET_TAKE_TIME=?,"
				+ "TICKET_TAKE_IP=?,"
				+ "TICKET_WAIT_COUNT=?,"
				+ "TICKET_CALL_TIME=?,"
				+ "TICKET_CALL_COUNT=?,"
				+ "TICKET_CALL_IP=?,"
				+ "TICKET_CALL_WINDOW=?,"
				+ "TICKET_CALLER_WORKNO=?,"
				+ "TICKET_IS_SUCCESS=?,"
				+ "TICKET_DESC=? "
				+ "where ticket_id=?";
		try {
			conn=JdbcUtil.getConnection();
			pstmst=conn.prepareStatement(sql);
			
			pstmst.setObject(1, ticket.getNo());
			pstmst.setObject(2, ticket.getBusinessCode());
			pstmst.setObject(3, ticket.getBusinessName());
			pstmst.setObject(4, ticket.getTakeTime());
			pstmst.setObject(5, ticket.getTakeIp());
			pstmst.setObject(6, ticket.getWaitCount());
			pstmst.setObject(7, ticket.getCallTime());
			pstmst.setObject(8, ticket.getCallCount());
			pstmst.setObject(9, ticket.getCallIp());
			pstmst.setObject(10, ticket.getCallWindow());
			pstmst.setObject(11, ticket.getCallerWorkno());
			pstmst.setObject(12, ticket.getIsSuccess());
			pstmst.setObject(13, ticket.getDesc());
			pstmst.setObject(14, ticket.getId());
			
			row=pstmst.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JdbcUtil.close(null,pstmst,conn);
		}
		return row;
	}


	@Override
	public Ticket get(int id) {
		String sql="select * from t_ticket where ticket_id=?";
		
		try {
			conn=JdbcUtil.getConnection();
			pstmst=conn.prepareStatement(sql);
			
			//设置参数
			pstmst.setObject(1, id);
			rs=pstmst.executeQuery();
			if(rs.next()) {
				Ticket tk=new Ticket();
				tk.setId(rs.getInt("TICKET_ID"));
				tk.setNo(rs.getInt("TICKET_NO"));
				tk.setBusinessCode(rs.getString("TICKET_BUSINESS_CODE"));
				tk.setBusinessName(rs.getString("TICKET_BUSINESS_NAME"));
				tk.setTakeTime(rs.getDate("TICKET_TAKE_TIME"));
				tk.setTakeIp(rs.getString("TICKET_TAKE_IP"));
				tk.setWaitCount(rs.getInt("TICKET_WAIT_COUNT"));
				tk.setCallTime(rs.getDate("TICKET_CALL_TIME"));
				tk.setCallCount(rs.getInt("TICKET_CALL_COUNT"));
				tk.setCallIp(rs.getString("TICKET_CALL_IP"));
				tk.setCallWindow(rs.getInt("TICKET_CALL_WINDOW"));
				tk.setCallerWorkno(rs.getString("TICKET_CALLER_WORKNO"));
				tk.setIsSuccess(rs.getInt("TICKET_IS_SUCCESS"));
				tk.setDesc(rs.getString("TICKET_DESC"));
				
				return tk;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JdbcUtil.close(null,pstmst,conn);
		}
		return null;
	}


	@Override
	public List<Ticket> findAll() {
		return null;
	}

	@Override
	public int getWaitConutToday(String bussinessType) {
		String sql="select count(1) cnt from t_ticket where ticket_business_code=?" //指定的业务类型
				+ " and date(TICKET_TAKE_TIME)=CURRENT_DATE()" //今天
				+ " and TICKET_CALL_TIME is null";  //等待中
		
		try {
			conn=JdbcUtil.getConnection();
			pstmst=conn.prepareStatement(sql);
			
			pstmst.setObject(1, bussinessType);
			
			rs=pstmst.executeQuery();
			if(rs.next()) {
				row=rs.getInt("cnt");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JdbcUtil.close(rs,pstmst,conn);
		}
		return row;
	}

	@Override
	public int getMaxTicketNoToday(String bussinessType) {
		String sql="select max(ticket_no) max_no from t_ticket where ticket_business_code=?"
				+ " and date(TICKET_TAKE_TIME)=CURRENT_DATE()";
		
		try {
			conn=JdbcUtil.getConnection();
			pstmst=conn.prepareStatement(sql);
			
			pstmst.setObject(1, bussinessType);
			
			rs=pstmst.executeQuery();
			if(rs.next()) {
				int maxTicketNo=rs.getInt("max_no");
				return maxTicketNo;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JdbcUtil.close(rs,pstmst,conn);
		}
		return 0;
	}
	
	@Override
	public Ticket findTodayFirstWaitingTicket(String code) {
		String sql="select * from t_ticket " //最小的,即最早取号
				+ "where ticket_business_code=? "  //业务类型匹配
				+ "and date(TICKET_TAKE_TIME)=CURRENT_DATE() " //今天
				+ "and TICKET_CALL_TIME is null "  //没有叫过号的
				+ "order by ticket_id asc limit 0,1"; //排序后，获取第一行数据
		try {
			conn=JdbcUtil.getConnection();
			pstmst=conn.prepareStatement(sql);
			
			pstmst.setObject(1, code);
			
			rs=pstmst.executeQuery();
			if(rs.next()) {
				Ticket tk=new Ticket();
				tk.setId(rs.getInt("TICKET_ID"));
				tk.setNo(rs.getInt("TICKET_NO"));
				tk.setBusinessCode(rs.getString("TICKET_BUSINESS_CODE"));
				tk.setBusinessName(rs.getString("TICKET_BUSINESS_NAME"));
				tk.setTakeTime(rs.getDate("TICKET_TAKE_TIME"));
				tk.setTakeIp(rs.getString("TICKET_TAKE_IP"));
				tk.setWaitCount(rs.getInt("TICKET_WAIT_COUNT"));
				tk.setCallTime(rs.getDate("TICKET_CALL_TIME"));
				tk.setCallCount(rs.getInt("TICKET_CALL_COUNT"));
				tk.setCallIp(rs.getString("TICKET_CALL_IP"));
				tk.setCallIp(rs.getString("TICKET_CALL_WINDOW"));
				tk.setCallerWorkno(rs.getString("TICKET_CALLER_WORKNO"));
				tk.setIsSuccess(rs.getInt("TICKET_IS_SUCCESS"));
				tk.setDesc(rs.getString("TICKET_DESC"));
				
				return tk;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JdbcUtil.close(rs,pstmst,conn);
		}
		return null;
	}
	
	public static void main(String[] args) {
		TicketDaoImpl dao=new TicketDaoImpl();
		
//		Ticket ticket=new Ticket();
//		ticket.setNo(001);
//		ticket.setBusinessCode("A");
//		ticket.setBusinessName("VIP业务");
//		ticket.setTakeTime(new Date());
//		ticket.setTakeIp("192.168.5.112");
//		ticket.setWaitCount(10);	
//		int ret=dao.add(ticket);
//		System.out.println("ret:"+ret);
		
//		int waitCnt=dao.getWaitConutToday("A");
//		System.out.println(waitCnt);
	
//		int maxNoToday=dao.getMaxTicketNoToday("A");
//		System.out.println(maxNoToday);
		
		Ticket ticket=dao.findTodayFirstWaitingTicket("C");
		System.out.println(ticket);
		ticket.setCallIp("127.0.0.1");
		int ret=dao.update(ticket);
		System.out.println(ret);
	}
}
